USE [Weval test]
GO

/****** Object:  StoredProcedure [dbo].[DepartmentFiveYear]    Script Date: 7/8/2013 7:21:23 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[DepartmentFiveYear] @CRN int, @year int, @semester int
AS
BEGIN


--SET NOCOUNT ON;

--DECLARE @CRN int = 30626, @semester int = 3, @year int = 2013;
DECLARE @subjectCode nvarchar(4)= --Ex: 'CS'
(
	SELECT TOP 1 subjectcode FROM CourseSections CS WHERE CS.bannerCRN = @CRN AND CS.year = @year AND cs.semester = @semester
);
DECLARE @class nvarchar(20)= --Ex: '1010'
(
	SELECT TOP 1 CS.courseNumber FROM CourseSections CS WHERE CS.bannerCRN = @CRN AND CS.year = @year AND cs.semester = @semester
);





WITH cteMain AS
(
	SELECT 
		CASE 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly disagree' THEN 0.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'very poor' THEN 0.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'disagree' THEN 1.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'poor' THEN 1.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'neutral' THEN 2.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'average' THEN 2.0
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'agree' THEN 3.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'good' THEN 3.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly agree' THEN 4.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'excellent' THEN 4.0
		END Score,
		CS.courseNumber
		
	FROM SectionsTaught ST
			INNER JOIN Results R ON R.sectionID = ST.sectionID
				AND (R.deleted = 0 OR R.deleted IS NULL)
			INNER JOIN CourseSections CS ON CS.ID = R.sectionID
				AND CS.subjectCode = @subjectCode
				--AND CS.bannerCRN = @CRN
				AND CS.year > (@year-6)
			INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
			INNER JOIN Questions Q ON Q.ID = QR.questionID
			INNER JOIN Categories C on C.ID = Q.categoryID
				AND LOWER(C.categoryName) <> 'other' 
			INNER JOIN Tests T ON T.ID = Q.testID 
				AND T.typeID = 5 --Course Eval
			INNER JOIN DistractorResponses DR ON DR.questionResponseID = QR.ID
			INNER JOIN Distractors D ON D.ID = DR.distractorID
			--INNER JOIN Users U ON U.Id = R.studentID
			--INNER JOIN Deliveries DEL ON DEL.ID = R.deliveryID
)

SELECT
	AVG(score) DepartmentFiveYearAverage,
	STDEVP(score) DepartmentFiveYearSTDeviation,
	AVG(CASE WHEN courseNumber = @class THEN score END) ClassFiveYearAverage,
	STDEVP(CASE WHEN courseNumber = @class THEN score END) ClassFiveYearSTDeviation

FROM cteMain

END
GO

